USE BigDB
go
-- This procedures demonstrates a general chunking. By using TOP +
-- MAX, we can move @chunksize rows ahead. The beauty of it is that
-- it works with any data type, and there is no problems with gaps.
CREATE OR ALTER  PROCEDURE insert_top_plain @chunksize int AS
   DECLARE @minID  int,
           @maxID  int

   SELECT @minID = MIN(TrnID) FROM BigTrans

   WHILE @minID IS NOT NULL
   BEGIN
      SELECT @maxID = MAX(TrnID)
      FROM   (SELECT TOP(@chunksize) TrnID
              FROM   BigTrans
              WHERE  TrnID >= @minID
              ORDER  BY TrnID ASC) AS B

      INSERT NewTrans(TrnID, ProdID, TrnDate, Qty, Amount)
        SELECT TrnID, ProdID, TrnDate, Qty, Amount
        FROM   BigTrans
        WHERE  TrnID BETWEEN @minID AND @maxID

      SELECT @minID = MIN(TrnID) FROM BigTrans WHERE TrnID > @maxID
   END

   CREATE NONCLUSTERED INDEX IX_ProdID_TrnDate
     ON NewTrans(ProdID, TrnDate) INCLUDE (Qty, Amount)

   ALTER TABLE NewTrans ADD CONSTRAINT fk_NewTrans_Product
      FOREIGN KEY (ProdID) REFERENCES Product (ProductID)
go
-- This is a more specialised version that gives slightly better 
-- performance than the above for batchsizes >= 30000 (but actually
-- is somewhat slower for a chunk size of 1000) in the scenario where 
-- you have a numeric key which is largely contiguous. It will not 
-- work well with a table where the id column has longer gaps.
CREATE OR ALTER PROCEDURE insert_plain_plain @chunksize int AS
   DECLARE @minID  int,
           @maxID  int,
           @stopID int

   SELECT @minID = MIN(TrnID) FROM BigTrans
   SELECT @stopID = MAX(TrnID) FROM BigTrans

   WHILE @minID <= @stopID
   BEGIN
      SELECT @maxID = @minID + @chunksize - 1

      INSERT NewTrans(TrnID, ProdID, TrnDate, Qty, Amount)
        SELECT TrnID, ProdID, TrnDate, Qty, Amount
        FROM   BigTrans
        WHERE  TrnID BETWEEN @minID AND @maxID

      SELECT @minID = @maxID + 1
   END

   CREATE NONCLUSTERED INDEX IX_ProdID_TrnDate
     ON NewTrans(ProdID, TrnDate) INCLUDE (Qty, Amount)

   ALTER TABLE NewTrans ADD CONSTRAINT fk_NewTrans_Product
      FOREIGN KEY (ProdID) REFERENCES Product (ProductID)
go
-- This procedure includes OPTION (RECOMPILE). In this particular
-- example it decreases the performance even at bigger chunk sizes,
-- because of an optimization that backfires. But as a principle, 
---you should always have one of this or the dynamic SQL below.
CREATE OR ALTER PROCEDURE insert_top_recompile @chunksize int AS
   DECLARE @minID  int,
           @maxID  int

   SELECT @minID = MIN(TrnID) FROM BigTrans

   WHILE @minID IS NOT NULL
   BEGIN
      SELECT @maxID = MAX(TrnID)
      FROM   (SELECT TOP(@chunksize) TrnID
              FROM   BigTrans
              WHERE  TrnID >= @minID
              ORDER  BY TrnID ASC) AS B

      INSERT NewTrans(TrnID, ProdID, TrnDate, Qty, Amount)
        SELECT TrnID, ProdID, TrnDate, Qty, Amount
        FROM   BigTrans
        WHERE  TrnID BETWEEN @minID AND @maxID
      OPTION (RECOMPILE)

      SELECT @minID = MIN(TrnID)
      FROM   BigTrans
      WHERE  TrnID > @maxID
   END

   CREATE NONCLUSTERED INDEX IX_ProdID_TrnDate
     ON NewTrans(ProdID, TrnDate) INCLUDE (Qty, Amount)

   ALTER TABLE NewTrans ADD CONSTRAINT fk_NewTrans_Product
      FOREIGN KEY (ProdID) REFERENCES Product (ProductID)
go
-- Rather than using OPTION (RECOMPILE), I wrap the statement
-- with the interval in dynamic SQL, so that @minID and @maxID
-- can be sniffed by the optimizer, which permits for a better
-- plan. This avoids compilation overhead at low batch sizes.
CREATE OR ALTER PROCEDURE insert_top_dynsql @chunksize int AS
   DECLARE @minID  int,
           @maxID  int

   SELECT @minID = MIN(TrnID) FROM BigTrans

   WHILE @minID IS NOT NULL
   BEGIN
      SELECT @maxID = MAX(TrnID)
      FROM   (SELECT TOP(@chunksize) TrnID
              FROM   BigTrans
              WHERE  TrnID >= @minID
              ORDER  BY TrnID ASC) AS B

      EXEC sp_executesql
         N'INSERT NewTrans(TrnID, ProdID, TrnDate, Qty, Amount)
           SELECT TrnID, ProdID, TrnDate, Qty, Amount
           FROM   dbo.BigTrans
           WHERE  TrnID BETWEEN @minID AND @maxID',
        N'@minID int, @maxID int', @minID, @maxID

      SELECT @minID = MIN(TrnID)
      FROM   BigTrans
      WHERE  TrnID > @maxID
   END

   CREATE NONCLUSTERED INDEX IX_ProdID_TrnDate
     ON NewTrans(ProdID, TrnDate) INCLUDE (Qty, Amount)

   ALTER TABLE NewTrans ADD CONSTRAINT fk_NewTrans_Product
      FOREIGN KEY (ProdID) REFERENCES Product (ProductID)
go